/*
 exec sp_save_tape_reel_request_lot_details 54, 'oseplot5.00', 9625, 950,1000, 900,25,25,'06/27/2007',942, 62,1
select * from lot_table where lot_number = 'oseplot5.00'
 exec sp_save_tape_reel_request_lot_details 51, 'fglot5.00', 9603, 950,1000, 0,0,0,'06/27/2007',1019, 62,1
select * from lot_table where lot_number = 'oseplot4.00'
cancle - 0
*/
Alter  PROC sp_save_tape_reel_request_lot_details
(
	@Tape_Reel_Request_Id INT,
	@lot_number VARCHAR(50),
	@lot_id INT,				
	@qty_pulled INT,
	@available_qty INT,
	@tr_qty INT,
	@return_Qty INT,
	@reject_Qty INT,
	@req_date VARCHAR(50),
	@tr_processStep_id INT,
	@tr_location_id INT,	
	@is_cancelled BIT
)
AS
BEGIN
	DECLARE @ROW_COUNT INT
	DECLARE @fg_location_id INT
	DECLARE @fg_processStep_id INT
	DECLARE @Prev_Qty_Pulled INT
	DECLARE @Prev_TR_Qty INT
	DECLARE @Prev_Return_Qty INT
	DECLARE @Prev_Reject_Qty INT
	DECLARE @IL_Qty_Balance INT
	DECLARE @TR_LOT_ID INT
	DECLARE @Assembly_Vendor VARCHAR(50)
	DECLARE @Status VARCHAR(50)
	DECLARE @LOT_CATEGORY VARCHAR(10)
	declare @CSProcessstep_id int 
	declare @CSLot_id int	
	declare @CSLocationId int
	declare @Vendor varchar(50)
	declare @lotStatus varchar(50)

	SELECT @Status = Status FROM Tape_Reel_Request_Table WHERE Tape_Reel_Request_Id = @Tape_Reel_Request_Id
	
	IF( @Status IS NOT NULL AND @Status = 'Issued' AND @qty_pulled > 0 )
	BEGIN
		UPDATE Tape_Reel_REQUEST_TABLE SET status = 'Pulled', Time_Stamp = CURRENT_TIMESTAMP WHERE Tape_Reel_Request_Id = @Tape_Reel_Request_Id		
	END

	--Getting Tape & Reel processstepId and checking if the lot exists in Tape & Reel
	SET @CSLot_id = 0
	set @CSLocationId = 0
	set @CSProcessstep_id = 0

	select @CSProcessstep_id = processstep_id from fn_get_process_flow_by_processstep_id ( @tr_processStep_id ) where ps_type = 'Tape & Reel'
	select @Vendor = L_Vendor_Name from location where location_id = @tr_location_id
	select @CSLocationId = location_id from location where L_Vendor_Name = @Vendor and L_Type = 'Class Store'

	if @CSLocationId is null or @CSLocationId = 0
	begin
		set @CSLocationId = @tr_location_id
	end

	SELECT @CSLot_id = Lot_ID FROM Lot_Table WHERE ProcessStep_ID = @CSProcessstep_id AND Location_ID = @CSLocationId AND Lot_Number = @lot_number	

	SELECT @row_count = COUNT(*) FROM TAPE_REEL_REQUEST_LOT_TABLE WHERE TAPE_REEL_REQUEST_ID = @Tape_Reel_Request_Id AND LOT_ID = @lot_id
	
	--Need to check not to get previously closed fg lots
	SET @TR_LOT_ID = 0
	SELECT @TR_LOT_ID = Lot_ID FROM Lot_Table WHERE ProcessStep_ID = @tr_processStep_id AND Location_ID = @tr_location_id AND Lot_Number = @lot_number

	IF( @row_count > 0 )
	BEGIN
		IF ( @qty_pulled > 0 AND @is_cancelled = 1 )
		BEGIN
			--Check if their is any change from the previous qty.
			-- If there is a change in the Qty update the information.
			SELECT @Prev_Qty_Pulled = Qty_Pulled, @Prev_TR_Qty = TR_Qty, @Prev_Return_Qty = Return_Qty, @Prev_Reject_Qty = Reject_Qty FROM TAPE_REEL_REQUEST_LOT_TABLE WHERE TAPE_REEL_REQUEST_ID = @Tape_Reel_Request_Id AND LOT_ID = @lot_id

			-- UPDATE THE PULLED LOT INFORMATION
			-- Update Tape and Reel Lot Table
			UPDATE TAPE_REEL_REQUEST_LOT_TABLE SET Qty_Pulled = @qty_pulled, TR_Qty = @tr_qty, Return_Qty = @return_Qty, Reject_Qty = @reject_Qty WHERE TAPE_REEL_REQUEST_ID = @Tape_Reel_Request_Id AND LOT_ID = @lot_id
			--Update lot and inventory lot table for TR Lots

			IF ( @tr_qty > 0 or @return_Qty > 0 or @reject_Qty > 0 )
			BEGIN
				select @lotStatus = lot_current_status from lot_table where lot_id = @CSLot_id
				--lot status is running then only update
				if @lotStatus = 'running'
				begin
					UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN - ( @tr_qty + @return_Qty + @reject_Qty) ) WHERE Lot_ID = @CSLot_id
					UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN - ( @tr_qty + @return_Qty + @reject_Qty) ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) - ( @tr_qty + @return_Qty + @reject_Qty) ) WHERE Lot_ID = @CSLot_id
					SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @CSLot_id
					IF @IL_Qty_Balance <= 0
					BEGIN
						UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @CSLot_id
					END
					ELSE
					BEGIN
						UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @CSLot_id
					END
				end
			END
			
			-- Update the non tape and reel inventory
			
			if @Prev_Return_Qty > 0 or  @Prev_Reject_Qty > 0 
			begin
				UPDATE LOT_TABLE SET QTY_IN = ( ( QTY_IN + @Prev_Qty_Pulled - @Prev_Return_Qty - @Prev_Reject_Qty ) - @qty_pulled + @return_Qty + @reject_Qty ) WHERE Lot_ID = @lot_id
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( ( IL_QTY_IN + @Prev_Qty_Pulled - @Prev_Return_Qty - @Prev_Reject_Qty ) - @qty_pulled + @return_Qty + @reject_Qty ), IL_Qty_Balance = ( ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @Prev_Qty_Pulled - @Prev_Return_Qty - @Prev_Reject_Qty ) - @qty_pulled + @return_Qty + @reject_Qty ) WHERE Lot_ID = @lot_id
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @lot_id
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @lot_id
				END
			end
			else
			begin
				UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN + @return_Qty ) WHERE Lot_ID = @lot_id
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN + @return_Qty  ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @return_Qty  ) WHERE Lot_ID = @lot_id
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @lot_id
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @lot_id
				END
			end
			-- Update Tape and Reel Lot Qty
			IF @TR_LOT_ID IS NOT NULL AND @TR_LOT_ID != 0
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = ( ( QTY_IN + @tr_qty ) - @Prev_TR_Qty ) WHERE Lot_ID = @TR_LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( ( IL_QTY_IN + @tr_qty ) - @Prev_TR_Qty ), IL_Qty_Balance = ( ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @tr_qty ) - @Prev_TR_Qty ) WHERE Lot_ID = @TR_LOT_ID
				SET @IL_Qty_Balance = 0
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @TR_LOT_ID
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @TR_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @TR_LOT_ID
				END
			END
			ELSE IF @tr_qty > 0
			BEGIN
				SELECT @LOT_CATEGORY = LOT_CATEGORY FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

				INSERT INTO Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_lot_Number, vendor_lot_number, Lot_Category, Qty_In, Date_In, Lot_Current_Status, Timestamp, Lot_Entered_By ) VALUES ( @tr_processStep_id, @tr_location_id, @lot_number, @lot_number, @lot_number, @LOT_CATEGORY,@tr_qty, @req_date, 'Running', CURRENT_TIMESTAMP, 0)
				SELECT @TR_LOT_ID = @@IDENTITY			
				INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP) VALUES (@TR_LOT_ID, @tr_qty, @req_date, CURRENT_TIMESTAMP )
			END
		END
		ELSE
		BEGIN
			-- Lot Previously PULLED but Returned back.
			SELECT @Prev_Qty_Pulled = Qty_Pulled, @Prev_TR_Qty = TR_Qty, @Prev_Return_Qty = Return_Qty, @Prev_Reject_Qty = Reject_Qty FROM TAPE_REEL_REQUEST_LOT_TABLE WHERE TAPE_REEL_REQUEST_ID = @Tape_Reel_Request_Id AND LOT_ID = @lot_id
			-- UPDATE THE PULLED LOT INFORMATION
			-- Update Tape and Reel Lot Table
			IF ( @is_cancelled = 1 )
			BEGIN
				UPDATE TAPE_REEL_REQUEST_LOT_TABLE SET Qty_Pulled = 0, TR_Qty = 0, Return_Qty = 0, Reject_Qty = 0 WHERE TAPE_REEL_REQUEST_ID = @Tape_Reel_Request_Id AND LOT_ID = @lot_id
			END
			--Update lot and inventory lot table for TR Lots

			IF ( @tr_qty = 0 and @return_Qty = 0 and @reject_Qty = 0 )
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN - @qty_pulled ) WHERE Lot_ID = @CSLot_id
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN - ( @qty_pulled ) ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) - ( @qty_pulled ) ) WHERE Lot_ID = @CSLot_id
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @CSLot_id
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @CSLot_id
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @CSLot_id
				END
			END
			
			-- Update the non tape and reel inventory
			UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN + @Prev_Qty_Pulled - @Prev_Return_Qty  ) WHERE Lot_ID = @lot_id
			UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN + @Prev_Qty_Pulled - @Prev_Return_Qty ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @Prev_Qty_Pulled - @Prev_Return_Qty ) WHERE Lot_ID = @lot_id
			SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @lot_id
			IF @IL_Qty_Balance <= 0
			BEGIN
				UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
			END
			ELSE
			BEGIN
				UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @lot_id
			END

			-- Update Tape and Reel Lot Qty
			IF @TR_LOT_ID IS NOT NULL AND @TR_LOT_ID != 0
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN - @Prev_TR_Qty ) WHERE Lot_ID = @TR_LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN - @Prev_TR_Qty ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) - @Prev_TR_Qty ) WHERE Lot_ID = @TR_LOT_ID
				SET @IL_Qty_Balance = 0
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @TR_LOT_ID
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @TR_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @TR_LOT_ID
				END
			END
		END	
	END
	ELSE
	BEGIN
		IF @qty_pulled > 0
		BEGIN
			-- UPDATE THE PULLED LOT INFORMATION
			-- Update Tape and Reel Lot Table
			INSERT INTO TAPE_REEL_REQUEST_LOT_TABLE ( TAPE_REEL_REQUEST_ID, LOT_ID, Qty_Pulled, TR_Qty, Status, Return_Qty, Reject_Qty ) Values ( @Tape_Reel_Request_Id, @lot_id, @qty_pulled, @tr_qty, 'Pulled', @return_Qty, @reject_Qty )

			IF ( @tr_qty = 0 and @return_Qty = 0 and @reject_Qty = 0 )
			BEGIN
				--if lot exists the update the qty otherwise insert new lot in Tape & Reel.
				IF @CSLot_id IS NOT NULL AND @CSLot_id != 0
				BEGIN
					UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN + @qty_pulled ), Lot_Current_Status = 'Running' WHERE Lot_ID = @CSLot_id
					UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN + @qty_pulled ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @qty_pulled ) WHERE Lot_ID = @CSLot_id
				END
				ELSE 
				BEGIN
					SELECT @LOT_CATEGORY = LOT_CATEGORY FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	
					INSERT INTO Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_CATEGORY, Qty_In, Date_In, Lot_Current_Status, Timestamp, Lot_Entered_By ) VALUES ( @CSProcessstep_id, @CSLocationId, @lot_number, @lot_number, @lot_number, @LOT_CATEGORY, @qty_pulled, @req_date, 'Running', CURRENT_TIMESTAMP, 0)
					SELECT @CSLot_id = @@IDENTITY			
					SELECT @Assembly_Vendor = ASSEMBLY_VENDOR FROM INVENTORY_LOT WHERE LOT_ID = @lot_id
					INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP, ASSEMBLY_VENDOR ) VALUES (@CSLot_id, @qty_pulled, @req_date, CURRENT_TIMESTAMP, @Assembly_Vendor )
				END
			END
			
			-- Update the non tape and reel inventory
			UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN - @qty_pulled + @return_Qty ) WHERE Lot_ID = @lot_id
			UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN - @qty_pulled + @return_Qty ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) - @qty_pulled + @return_Qty ) WHERE Lot_ID = @lot_id
			SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @lot_id
			IF @IL_Qty_Balance <= 0
			BEGIN
				UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
			END
			ELSE
			BEGIN
				UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @lot_id
			END

			-- Update Tape and Reel Lot Qty
			IF @TR_LOT_ID IS NOT NULL AND @TR_LOT_ID != 0
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN + @tr_qty ) WHERE Lot_ID = @TR_LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( IL_QTY_IN + @tr_qty ), IL_Qty_Balance = ( ISNULL( IL_Qty_Balance, IL_QTY_IN ) + @tr_qty ) WHERE Lot_ID = @TR_LOT_ID
				SET @IL_Qty_Balance = 0
				SELECT @IL_Qty_Balance = ISNULL( IL_Qty_Balance, IL_QTY_IN ) FROM Inventory_Lot WHERE LOT_ID = @TR_LOT_ID
				IF @IL_Qty_Balance <= 0
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @TR_LOT_ID
				END
				ELSE
				BEGIN
					UPDATE Lot_Table SET Lot_Current_Status = 'Running' WHERE Lot_ID = @TR_LOT_ID
				END
			END
			ELSE IF @tr_qty > 0
			BEGIN
				SELECT @LOT_CATEGORY = LOT_CATEGORY FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

				INSERT INTO Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_CATEGORY, Qty_In, Date_In, Lot_Current_Status, Timestamp, Lot_Entered_By ) VALUES ( @tr_processStep_id, @tr_location_id, @lot_number, @lot_number, @lot_number, @LOT_CATEGORY, @tr_qty, @req_date, 'Running', CURRENT_TIMESTAMP, 0)
				SELECT @TR_LOT_ID = @@IDENTITY			
				SELECT @Assembly_Vendor = ASSEMBLY_VENDOR FROM INVENTORY_LOT WHERE LOT_ID = @lot_id
				INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP, ASSEMBLY_VENDOR ) VALUES (@TR_LOT_ID, @tr_qty, @req_date, CURRENT_TIMESTAMP, @Assembly_Vendor )
			END
		END
	END	
END --End of proc

